﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using ExcelPrinter;


namespace XM08000
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private string strConnectionString = "";
        private int CommandTimeOut = 300;
 

        private void btn_Export_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure to export Shortage List by Rotation data now?", "Question", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK)
            {
                return;
            }
            this.Cursor = Cursors.WaitCursor;
            string strExcelFile;
            string strTempExcelFile;
            int iCol;
            int iRow;
            string sqlText;
            string sAppPath="";
            DataTable dtShortage = new DataTable();
            DataTable dtDate = new DataTable();

            strTempExcelFile = Application.StartupPath + @"\Shortage List by Rotation.xls";
            this.lb_Status.Text = "Check Excel template file.";
            this.lb_Status.Refresh();
            if (System.IO.File.Exists(strTempExcelFile) == false)
            {
                MessageBox.Show("The Excel template document does not exist","OK",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
            //sAppPath = IIf(VB.Right(Dir1.Path, 1) = @"\", Mid(Dir1.Path, 1, Len(Dir1.Path) - 1), Dir1.Path);
            sAppPath = @Dir1.Path.Substring(Dir1.Path.Length - 1, 1) == @"\" ? Dir1.Path.Substring(0, Dir1.Path.Length - 1) : Dir1.Path;
            strExcelFile = sAppPath + @"\Shortage List by Rotation_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            SqlConnection SqlCon = new SqlConnection(strConnectionString);

            try
            {
                this.lb_Status.Text = "Run Procedure xpShortage_RotationReport...";
                this.lb_Status.Refresh();
                sqlText = "xpShortage_RotationReport";
                SqlCon.Open();
                SqlCommand SqlCom = new SqlCommand();

                SqlParameter sqlPara1 = new SqlParameter();

                sqlPara1.ParameterName = "@Flag";
                sqlPara1.Direction = ParameterDirection.Input;
                sqlPara1.DbType = DbType.Int32;
                sqlPara1.Value = 1;

                SqlCom.CommandText = sqlText;
                SqlCom.CommandType = CommandType.StoredProcedure;
                SqlCom.CommandTimeout = CommandTimeOut;
                SqlCom.Connection = SqlCon;
                SqlCom.Parameters.Add(sqlPara1);

                DataSet ds1 = new DataSet();
                SqlDataAdapter SqlAdp = new SqlDataAdapter(SqlCom);

                SqlAdp.Fill(ds1);

                if ((ds1 == null) && (ds1.Tables.Count != 2))
                {
                    this.lb_Status.Text = "No  data exists.";
                    MessageBox.Show("No date  exists.", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                dtShortage = ds1.Tables[0];
                dtDate = ds1.Tables[1];


                if (dtDate == null || dtDate.Rows.Count == 0)
                {
                    this.lb_Status.Text = "No date data exists.";
                    MessageBox.Show("No date data exists.", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                iCol = dtDate.Rows.Count;

                string[,] ExcelCol = new string[1, dtDate.Rows.Count];

                for (int i = 0; i < dtDate.Rows.Count; i++)
                {
                    ExcelCol[0, i] = dtDate.Rows[i][0].ToString();
                }

                if (dtShortage == null || dtShortage.Rows.Count == 0)
                {
                    this.lb_Status.Text = "No shortage list data exists.";
                    MessageBox.Show("No shortage list data exists.", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                iRow = dtShortage.Rows.Count;
                object[,] DataArray = new object[dtShortage.Rows.Count, dtDate.Rows.Count + 4];
                string strDocType = "";
                for (int i = 0; i < dtShortage.Rows.Count; i++)
                {
                    DataArray[i, 0] = dtShortage.Rows[i]["InvtID"];
                    DataArray[i, 1] = dtShortage.Rows[i]["DocType"];
                    DataArray[i, 2] = dtShortage.Rows[i]["FG"];
                    DataArray[i, 3] = dtShortage.Rows[i]["Delay"];
                    strDocType = dtShortage.Rows[i]["DocType"].ToString().Trim();
                    int x = 4;
                    int y = 8;
                    for (int j = 0; j < dtDate.Rows.Count; j++)
                    {
                        string Data = dtShortage.Rows[i][y].ToString().Trim();
                        if ((strDocType != "PO Nbr") && (Data == ""))
                        {
                            DataArray[i, x] = 0;
                        }
                        else
                        {
                            DataArray[i, x] = dtShortage.Rows[i][y];
                        }
                        // DataArray[i, x] = dtShortage.Rows[i][y];
                        x = x + 1;
                        y = y + 1;
                    }
                }
                //this.lb_Status.Text = "Choose save path...";
                //this.saveFileDialog1.Filter = "(*.xls)|*.xls";
                //this.saveFileDialog1.FileName = "Shortage List by Rotation_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

                //if (saveFileDialog1.ShowDialog() != DialogResult.OK)
                //{
                //    this.lb_Status.Text = "No choose save path,cancel.";
                //    return;
                //}
                //strExcelFile = this.saveFileDialog1.FileName;
                //strExcelFile = this.saveFileDialog1.FileName;
                this.lb_Status.Text = "Write Data to Excel...";
                this.lb_Status.Refresh();
                
                ExcelAccess xlsExport = new ExcelAccess();
                xlsExport.Open(strTempExcelFile);
                Microsoft.Office.Interop.Excel.Range range;

                range = xlsExport.GetRange(1, 5, 1, 4 + iCol);
                range.Value2 = ExcelCol;

                range = xlsExport.GetRange(2, 1, iRow + 1, 4 + iCol);
                range.Value2 = DataArray;

                xlsExport.SaveAs(strExcelFile, true);
                xlsExport.Close();

                this.lb_Status.Text = "Export Completely."+"Row:" +iRow.ToString();
                this.lb_Status.Refresh();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "OK", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                SqlCon.Close();
                this.Cursor = Cursors.Default;
            }
        }

        private void btn_Close_Click(object sender, EventArgs e)
        {
            GC.Collect();
            this.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.lb_Status.Text = "";
            strConnectionString = "server=" + System.Configuration.ConfigurationManager.AppSettings["DBServer"] + ";database=" + System.Configuration.ConfigurationManager.AppSettings["DBCatalog"] + ";user id=" + System.Configuration.ConfigurationManager.AppSettings["UserID"] + ";password=" + System.Configuration.ConfigurationManager.AppSettings["Password"] + ";connect timeout=" + System.Configuration.ConfigurationManager.AppSettings["ConnectionTimeOut"] + "";
            CommandTimeOut = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["CommandTimeout"]);

            this.toolStripStatusLabel1.Text = System.Configuration.ConfigurationManager.AppSettings["DBServer"];
            this.toolStripStatusLabel2.Text = System.Configuration.ConfigurationManager.AppSettings["DBCatalog"];

        }

        private void Dir1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                this.Drive1.Drive = this.Dir1.Path;
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "OK", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void Drive1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                this.Dir1.Path = this.Drive1.Drive;
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "OK", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}
